package com.example.zjsr.pojo;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;

public class ExcelParser {
        public static void createExcel(String path, HashMap<String,Double> map,ExpRes res) {
            Workbook wb = new XSSFWorkbook();
            Sheet sheet = wb.createSheet("跨境电商保税进口虚拟仿真实验");
            sheet.autoSizeColumn(0);
            Row[] rows = new Row[31];
            XSSFFont font1 = (XSSFFont) wb.createFont();

            CellStyle style = wb.createCellStyle();
            Font font = wb.createFont();
            font.setBold(true);
            style.setFont(font);
            style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            addBorder(style);
            CellStyle cellStyle = wb.createCellStyle();
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            addBorder(cellStyle);

            /**
             * 第一行 实验名称 实验开始时间
             */
            rows[0]=sheet.createRow(0);
            Cell firstCell0=rows[0].createCell(0);
            firstCell0.setCellStyle(cellStyle);
            firstCell0.setCellValue("实验名称");
            Cell firstCell1=rows[0].createCell(1);
            firstCell1.setCellStyle(cellStyle);
            firstCell1.setCellValue("跨境电商保税进口虚拟仿真实验");
            CellRangeAddress firstCell1Region=new CellRangeAddress(0,0,1,2);
            sheet.addMergedRegion(firstCell1Region);
            Cell firstCell2=rows[0].createCell(2);
            firstCell2.setCellStyle(cellStyle);
            Cell firstCell3=rows[0].createCell(3);
            firstCell3.setCellStyle(cellStyle);
            firstCell3.setCellValue("实验开始时间");
            Cell firstCell4=rows[0].createCell(4);
            firstCell4.setCellStyle(cellStyle);
            SimpleDateFormat sdf =   new SimpleDateFormat( " yyyy-MM-dd HH:mm:ss " );
            Date sd = new Date(res.getStartDate());
            firstCell4.setCellValue(sdf.format(sd));


            /**
             * 第二行 用户名 实验结束时间
             */
            rows[1]=sheet.createRow(1);
            Cell secondCell0=rows[1].createCell(0);
            secondCell0.setCellStyle(cellStyle);
            secondCell0.setCellValue("用户名");
            Cell secondCell1=rows[1].createCell(1);
            secondCell1.setCellStyle(cellStyle);
            secondCell1.setCellValue(res.getUsername());
            CellRangeAddress secondCell1Region=new CellRangeAddress(1,1,1,2);
            sheet.addMergedRegion(secondCell1Region);
            Cell secondCell2=rows[1].createCell(2);
            secondCell2.setCellStyle(cellStyle);
            Cell secondCell1RegionCell3=rows[1].createCell(3);
            secondCell1RegionCell3.setCellStyle(cellStyle);
            secondCell1RegionCell3.setCellValue("实验结束时间");
            Cell secondCell4=rows[1].createCell(4);
            secondCell4.setCellStyle(cellStyle);
            Date ed = new Date(res.getEndDate());
            secondCell4.setCellValue(sdf.format(ed));

            /**
             * 第三行 实验成绩 实验用时
             */
            rows[2]=sheet.createRow(2);
            Cell thirdCell0=rows[2].createCell(0);
            thirdCell0.setCellStyle(cellStyle);
            thirdCell0.setCellValue("实验成绩");
            Cell thirdCell0Cell1=rows[2].createCell(1);
            thirdCell0Cell1.setCellStyle(cellStyle);
            thirdCell0Cell1.setCellValue(res.getScore());
            CellRangeAddress thirdCell1Region=new CellRangeAddress(2,2,1,2);
            sheet.addMergedRegion(thirdCell1Region);
            Cell thirdCell1RegionCell2=rows[2].createCell(2);
            thirdCell1RegionCell2.setCellStyle(cellStyle);
            Cell thirdCell1RegionCell3=rows[2].createCell(3);
            thirdCell1RegionCell3.setCellStyle(cellStyle);
            thirdCell1RegionCell3.setCellValue("实验用时(分钟)");
            Cell thirdCell4=rows[2].createCell(4);
            thirdCell4.setCellStyle(cellStyle);
            int totalSecond = (int)( res.getEndDate()-res.getStartDate())/1000;
            int min = totalSecond / 60;
            int sec = totalSecond % 60;
            String timeUsedRes = min+":"+ (sec < 10 ? "0"+sec : sec);
            thirdCell4.setCellValue(timeUsedRes);

            /**
             * 前期准备 第五行
             */
            rows[5]=sheet.createRow(5);
            Cell partOne0=rows[5].createCell(0);
            partOne0.setCellStyle(cellStyle);
            partOne0.setCellValue(1);
            CellRangeAddress partOne0Region1=new CellRangeAddress(5,9,0,0);
            sheet.addMergedRegion(partOne0Region1);
            Cell partOne1=rows[5].createCell(1);
            partOne1.setCellStyle(cellStyle);
            partOne1.setCellValue("前期准备");
            CellRangeAddress partOne1Region1=new CellRangeAddress(5,9,1,1);
            sheet.addMergedRegion(partOne1Region1);
            Cell partOne2=rows[5].createCell(2);
            partOne2.setCellStyle(cellStyle);
            partOne2.setCellValue("备案登记");
            Cell partOne3=rows[5].createCell(3);
            partOne3.setCellStyle(cellStyle);
            partOne3.setCellValue(map.get("备案登记"));
            Cell partOne4=rows[5].createCell(4);
            partOne4.setCellStyle(cellStyle);
            partOne4.setCellValue(map.get("前期准备"));
            CellRangeAddress partOne4Region1=new CellRangeAddress(5,9,4,4);
            sheet.addMergedRegion(partOne4Region1);

            /**
             * 前期准备 第6行
             */
            rows[6]=sheet.createRow(6);
            Cell partOne61=rows[6].createCell(1);
            partOne61.setCellStyle(cellStyle);
            Cell partOne5=rows[6].createCell(2);
            partOne5.setCellStyle(cellStyle);
            partOne5.setCellValue("海关注册编码");
            Cell partOne6=rows[6].createCell(3);
            partOne6.setCellStyle(cellStyle);
            partOne6.setCellValue(map.get("海关注册编码"));
            Cell partOne7=rows[6].createCell(4);
            partOne7.setCellStyle(cellStyle);

            /**
             * 前期准备 第7行
             */
            rows[7]=sheet.createRow(7);
            Cell partOne71=rows[7].createCell(1);
            partOne71.setCellStyle(cellStyle);
            Cell partOne8=rows[7].createCell(2);
            partOne8.setCellStyle(cellStyle);
            partOne8.setCellValue("电子口岸IC卡");
            Cell partOne9=rows[7].createCell(3);
            partOne9.setCellStyle(cellStyle);
            partOne9.setCellValue(map.get("电子口岸IC卡"));
            Cell partOne10=rows[7].createCell(4);
            partOne10.setCellStyle(cellStyle);

            /**
             * 前期准备 第8行
             */
            rows[8]=sheet.createRow(8);
            Cell partOne81=rows[8].createCell(1);
            partOne81.setCellStyle(cellStyle);
            Cell partOne11=rows[8].createCell(2);
            partOne11.setCellStyle(cellStyle);
            partOne11.setCellValue("单一窗口注册备案");
            Cell partOne12=rows[8].createCell(3);
            partOne12.setCellStyle(cellStyle);
            partOne12.setCellValue(map.get("单一窗口注册备案"));
            Cell partOne13=rows[8].createCell(4);
            partOne13.setCellStyle(cellStyle);

            /**
             * 前期准备第9行
             */
            rows[9]=sheet.createRow(9);
            Cell partOne91=rows[9].createCell(1);
            partOne91.setCellStyle(cellStyle);
            Cell partOne14=rows[9].createCell(2);
            partOne14.setCellStyle(cellStyle);
            partOne14.setCellValue("电子口岸数据中心企业注册");
            Cell partOne15=rows[9].createCell(3);
            partOne15.setCellStyle(cellStyle);
            partOne15.setCellValue(map.get("电子口岸数据中心企业注册"));
            Cell partOne16=rows[9].createCell(4);
            partOne16.setCellStyle(cellStyle);

            /**
             *入区准备 第10行
             */
            rows[10]=sheet.createRow(10);
            Cell secondPart0=rows[10].createCell(0);
            secondPart0.setCellStyle(cellStyle);
            secondPart0.setCellValue("2");
            CellRangeAddress secondPart0Region1=new CellRangeAddress(10,13,0,0);
            sheet.addMergedRegion(secondPart0Region1);
            Cell secondPart1=rows[10].createCell(1);
            secondPart1.setCellStyle(cellStyle);
            secondPart1.setCellValue("入区准备");
            CellRangeAddress secondPart1Region1=new CellRangeAddress(10,13,1,1);
            sheet.addMergedRegion(secondPart1Region1);
            Cell secondPart2=rows[10].createCell(2);
            secondPart2.setCellStyle(cellStyle);
            secondPart2.setCellValue("商品备案");
            Cell secondPart3=rows[10].createCell(3);
            secondPart3.setCellStyle(cellStyle);
            secondPart3.setCellValue(map.get("商品备案"));
            Cell secondPart4=rows[10].createCell(4);
            secondPart4.setCellStyle(cellStyle);
            secondPart4.setCellValue(map.get("入区准备"));
            CellRangeAddress secondPart4Region1=new CellRangeAddress(10,13,4,4);
            sheet.addMergedRegion(secondPart4Region1);

            /**
             * 入区准备第11行
             */
            rows[11]=sheet.createRow(11);
            Cell secondPart5=rows[11].createCell(0);
            secondPart5.setCellStyle(cellStyle);
            Cell secondPart6=rows[11].createCell(1);
            secondPart6.setCellStyle(cellStyle);
            Cell secondPart7=rows[11].createCell(2);
            secondPart7.setCellStyle(cellStyle);
            secondPart7.setCellValue("账册申请、申报");
            Cell secondPart8=rows[11].createCell(3);
            secondPart8.setCellStyle(cellStyle);
            secondPart8.setCellValue(map.get("账册申请、申报"));
            Cell secondPart19=rows[11].createCell(4);
            secondPart19.setCellStyle(cellStyle);

            /**
             * 入区准备第12行
             */
            rows[12]=sheet.createRow(12);
            Cell secondPart9=rows[12].createCell(0);
            secondPart9.setCellStyle(cellStyle);
            Cell secondPart10=rows[12].createCell(1);
            secondPart10.setCellStyle(cellStyle);
            Cell secondPart11=rows[12].createCell(2);
            secondPart11.setCellStyle(cellStyle);
            secondPart11.setCellValue("货物运抵");
            Cell secondPart12=rows[12].createCell(3);
            secondPart12.setCellStyle(cellStyle);
            secondPart12.setCellValue(map.get("货物运抵"));
            Cell secondPart18=rows[12].createCell(4);
            secondPart18.setCellStyle(cellStyle);

            /**
             * 入区准备第13行
             */
            rows[13]=sheet.createRow(13);
            Cell secondPart13=rows[13].createCell(0);
            secondPart13.setCellStyle(cellStyle);
            Cell secondPart14=rows[13].createCell(1);
            secondPart14.setCellStyle(cellStyle);
            Cell secondPart15=rows[13].createCell(2);
            secondPart15.setCellStyle(cellStyle);
            secondPart15.setCellValue("报关、报检预录入");
            Cell secondPart16=rows[13].createCell(3);
            secondPart16.setCellStyle(cellStyle);
            secondPart16.setCellValue(map.get("报关、报检预录入"));
            Cell secondPart17=rows[13].createCell(4);
            secondPart17.setCellStyle(cellStyle);
            /**
             * 入区通关第14行
             */
            rows[14]=sheet.createRow(14);
            Cell thirdPart0=rows[14].createCell(0);
            thirdPart0.setCellStyle(cellStyle);
            thirdPart0.setCellValue("3");
            CellRangeAddress thirdPart0Region1=new CellRangeAddress(14,15,0,0);
            sheet.addMergedRegion(thirdPart0Region1);
            Cell thirdPart1=rows[14].createCell(1);
            thirdPart1.setCellStyle(cellStyle);
            thirdPart1.setCellValue("入区通关");
            CellRangeAddress thirdPart1Region1=new CellRangeAddress(14,15,1,1);
            sheet.addMergedRegion(thirdPart1Region1);
            Cell thirdPart2=rows[14].createCell(2);
            thirdPart2.setCellStyle(cellStyle);
            thirdPart2.setCellValue("过卡进区");
            Cell thirdPart3=rows[14].createCell(3);
            thirdPart3.setCellStyle(cellStyle);
            thirdPart3.setCellValue(map.get("过卡进区"));
            Cell thirdPart4=rows[14].createCell(4);
            thirdPart4.setCellStyle(cellStyle);
            thirdPart4.setCellValue(map.get("入区通关"));
            CellRangeAddress thirdPart4Region1=new CellRangeAddress(14,15,4,4);
            sheet.addMergedRegion(thirdPart4Region1);
            /**
             * 入区通关第15行
             */
            rows[15]=sheet.createRow(15);
            Cell thirdPart5=rows[15].createCell(0);
            thirdPart5.setCellStyle(cellStyle);
            Cell thirdPart6=rows[15].createCell(1);
            thirdPart6.setCellStyle(cellStyle);
            Cell thirdPart7=rows[15].createCell(2);
            thirdPart7.setCellStyle(cellStyle);
            thirdPart7.setCellValue("查验、报关");
            Cell thirdPart8=rows[15].createCell(3);
            thirdPart8.setCellStyle(cellStyle);
            thirdPart8.setCellValue(map.get("查验、报关"));
            Cell thirdPart9=rows[15].createCell(4);
            thirdPart9.setCellStyle(cellStyle);

            /**
             * 出区通关第16行
             */
            rows[16]=sheet.createRow(16);
            Cell fourPart0=rows[16].createCell(0);
            fourPart0.setCellStyle(cellStyle);
            fourPart0.setCellValue("4");
            CellRangeAddress fourPart0Region1=new CellRangeAddress(16,17,0,0);
            sheet.addMergedRegion(fourPart0Region1);
            Cell fourPart1=rows[16].createCell(1);
            fourPart1.setCellStyle(cellStyle);
            fourPart1.setCellValue("出区通关");
            CellRangeAddress fourPart1Region1=new CellRangeAddress(16,17,1,1);
            sheet.addMergedRegion(fourPart1Region1);
            Cell fourPart2=rows[16].createCell(2);
            fourPart2.setCellStyle(cellStyle);
            fourPart2.setCellValue("表单申报");
            Cell fourPart3=rows[16].createCell(3);
            fourPart3.setCellStyle(cellStyle);
            fourPart3.setCellValue(map.get("表单申报"));
            Cell fourPart4=rows[16].createCell(4);
            fourPart4.setCellStyle(cellStyle);
            fourPart4.setCellValue(map.get("出区通关"));
            CellRangeAddress fourPart4Region1=new CellRangeAddress(16,17,4,4);
            sheet.addMergedRegion(fourPart4Region1);



            /**
             * 出区通关第17行
             */
            rows[17]=sheet.createRow(17);
            Cell fivePart5=rows[17].createCell(0);
            fivePart5.setCellStyle(cellStyle);
            Cell fivePart6=rows[17].createCell(1);
            fivePart6.setCellStyle(cellStyle);
            Cell fivePart7=rows[17].createCell(2);
            fivePart7.setCellStyle(cellStyle);
            fivePart7.setCellValue("商品出库拣货");
            Cell fivePart8=rows[17].createCell(3);
            fivePart8.setCellStyle(cellStyle);
            fivePart8.setCellValue(map.get("商品出库拣货"));
            Cell fivePart9=rows[17].createCell(4);
            fivePart9.setCellStyle(cellStyle);
            /**
             * 尾程派送第18行
             */
            rows[18]=sheet.createRow(18);
            Cell sexPart0=rows[18].createCell(0);
            sexPart0.setCellStyle(cellStyle);
            sexPart0.setCellValue("5");
            Cell sexPart1=rows[18].createCell(1);
            sexPart1.setCellStyle(cellStyle);
            sexPart1.setCellValue("尾程派送");
            Cell sexPart2=rows[18].createCell(2);
            sexPart2.setCellStyle(cellStyle);
            sexPart2.setCellValue("尾程派送");
            Cell sexPart3=rows[18].createCell(3);
            sexPart3.setCellStyle(cellStyle);
            sexPart3.setCellValue(map.get("尾程派送"));
            Cell sexPart4=rows[18].createCell(4);
            sexPart4.setCellStyle(cellStyle);
            sexPart4.setCellValue(map.get("尾程派送总分"));
            /**
             * 合计分数第19行
             */
            rows[19]=sheet.createRow(19);
            Cell sevenPart0=rows[19].createCell(0);
            sevenPart0.setCellStyle(cellStyle);
            sevenPart0.setCellValue("合计分数");
            CellRangeAddress sevenPart0Region1=new CellRangeAddress(19,19,0,3);
            sheet.addMergedRegion(sevenPart0Region1);
            Cell sevenPart1=rows[19].createCell(1);
            sevenPart1.setCellStyle(cellStyle);
            Cell sevenPart2=rows[19].createCell(2);
            sevenPart2.setCellStyle(cellStyle);
            Cell sevenPart3=rows[19].createCell(3);
            sevenPart3.setCellStyle(cellStyle);
            Cell sevenPart4=rows[19].createCell(4);
            sevenPart4.setCellStyle(cellStyle);
            sevenPart4.setCellValue(res.getScore());
            rows[20]=sheet.createRow(20);
            Cell suggestionRow=rows[20].createCell(0);
            suggestionRow.setCellStyle(style);
            suggestionRow.setCellValue("实验总结");
            Cell suggestionRow1=rows[20].createCell(1);
            suggestionRow1.setCellStyle(style);
            CellRangeAddress suggestionRowRegion=new CellRangeAddress(20,20,0,1);
            sheet.addMergedRegion(suggestionRowRegion);
            Cell suggestionValueRow=rows[20].createCell(2);
            suggestionValueRow.setCellStyle(cellStyle);
            suggestionValueRow.setCellValue(res.getExperimentSuggest());
            Cell suggestionValueRow2=rows[20].createCell(3);
            suggestionValueRow2.setCellStyle(cellStyle);
            Cell suggestionValueRow3=rows[20].createCell(4);
            suggestionValueRow3.setCellStyle(cellStyle);
            CellRangeAddress suggestionValue2RowRegion=new CellRangeAddress(20,20,2,4);
            sheet.addMergedRegion(suggestionValue2RowRegion);
            /**
             * 分项成绩单
             */
            Row gradeRow=sheet.createRow(3);
            Cell gradeCell=gradeRow.createCell(0);
            gradeRow.createCell(0).setCellStyle(style);
            gradeCell.setCellValue("分享成绩单");
            CellRangeAddress region=new CellRangeAddress(3,3,0,4);
            sheet.addMergedRegion(region);
            rows[4] = sheet.createRow(4);
            Cell cell0 = rows[4].createCell(0);
            Cell cell1 = rows[4].createCell(1);
            Cell cell2 =  rows[4].createCell(2);
            Cell cell3 =  rows[4].createCell(3);
            Cell cell4 =  rows[4].createCell(4);
            cell0.setCellValue("序号");
            cell1.setCellValue("项目");
            cell2.setCellValue("类目");
            cell3.setCellValue("分项得分");
            cell4.setCellValue("总计");
            cell0.setCellStyle(style);
            cell1.setCellStyle(style);
            cell2.setCellStyle(style);
            cell3.setCellStyle(style);
            cell4.setCellStyle(style);
            sheet.setColumnWidth(0,10*256);
            sheet.setColumnWidth(1,10*265);
            sheet.setColumnWidth(2,23*256);
            sheet.setColumnWidth(3,20*256);
            sheet.setColumnWidth(4,20*256);
            try (OutputStream fileOut = new FileOutputStream(path)) {
                wb.write(fileOut);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        //增加4个border
        public static void addBorder(CellStyle style){
            style.setBorderBottom(BorderStyle.THIN);
            style.setBorderLeft(BorderStyle.THIN);
            style.setBorderRight(BorderStyle.THIN);
            style.setBorderTop(BorderStyle.THIN);
            style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
            style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
            style.setRightBorderColor(IndexedColors.BLACK.getIndex());
            style.setTopBorderColor(IndexedColors.BLACK.getIndex());
            style.setAlignment(HorizontalAlignment.CENTER);
        }
}
